import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class AccountDBManager extends AccountManager {

	public AccountDBManager(Connection myConnection, PropertyDBManager propertyDBManager, RankDBManager rankDBManager, BalanceDBManager balanceDBManager) {
		// constructor
		super(myConnection, propertyDBManager, rankDBManager, balanceDBManager); // use
		// super
		// class
		// constructor
		// for initialize
		// connection

	}

	public String longToString(Long number) {
		// convert long number to string ( use it for setString operation in DB
		// manager )

		return number.toString();
	}

	public String doubleToString(double number) {
		// convert double number to string ( use it for setString operation in
		// DB manager )
		String newString = Double.toString(number);

		return newString;
	}

	@Override
	public boolean addAccount(Account newAccount) throws DBManagerException {
		// Add new Account object to account table
		// return TRUE - in case successful add new account
		// return FALSE , with appropriate message if can't add account

		PropertyDBManager propertyDBManager = getPropertyDBManager();
		PreparedStatement pstmt = null; // define prepared statement
		int rowsInserted = 0; // number of inserted rows
		boolean flag = false;

		// update Account object by getting Commission and Credit from
		// rankDbManager
		Account currAccount = getRankDBManager().getComissionAndCreditForAccount(newAccount);

		Connection conn = getConnection(); // receive connection from Account
		// Manager

		try {
			pstmt = conn.prepareStatement("INSERT INTO accounts (account_id,cust_id,amount,credit,commission,comments) VALUES (?,?,?,?,?,?)");

			// recieve new account id for new Account from propertyDBManager
			Long accountId = propertyDBManager.getNextValue("account_sequence");
			pstmt.setString(1, longToString(accountId));

			Long custId = currAccount.getCustId();
			pstmt.setString(2, longToString(custId));

			double amount = currAccount.getAmount();
			pstmt.setDouble(3, amount);

			double credit = currAccount.getCredit();
			pstmt.setDouble(4, credit);

			double commission = currAccount.getCommission();
			pstmt.setDouble(5, commission);

			pstmt.setString(6, currAccount.getComments());

			rowsInserted = pstmt.executeUpdate(); // execute query

		} catch (SQLException e) {
			throw new DBManagerException("[AccountDBManager]: Error in insert data - please check you account inform!");

		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		if (rowsInserted != 0) {
			System.out.println("[AccountDBManager]: Success, new account was created ");
			flag = true;
		}
		return flag;

	}

	@Override
	public boolean deleteAccount(Account oldAccount) throws DBManagerException {
		// Delete old Account from account table
		// receive oldAccount object
		// return TRUE - in case successfully delete account
		// return FALSE , with appropriate message if can't delete this account

		PreparedStatement pstmt = null;
		Boolean flag = false;
		int rowsDeleted = 0; // number of deleted rows
		Connection conn = getConnection(); // receive connection from Account
		// Manager

		try {
			pstmt = conn.prepareStatement("DELETE FROM accounts WHERE account_id=(?) AND cust_id =(?)");

			pstmt.setString(1, longToString(oldAccount.getAccountId()));

			pstmt.setString(2, longToString(oldAccount.getCustId()));

			rowsDeleted = pstmt.executeUpdate();

		} catch (SQLException e) {
			throw new DBManagerException("[AccountDBManager]: Data wasn't found , number of deleted rows : " + rowsDeleted);
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		if (rowsDeleted != 0) {
			System.out.println("[AccountDBManager]: Success, account was deleted ");
			flag = true;
		}

		return flag;
	}

	@Override
	public boolean depositWithComm(Account currAccount, double moneyAmount) throws DBManagerException {

		boolean flag = false;

		BalanceDBManager balanceDBManager = getBalanceDBManager();

		// Calculate commission for this Account and moneyAmount
		double currComission = calculateComission(currAccount, moneyAmount);

		if (depositWithoutComm(currAccount, (moneyAmount - currComission))) {

			// Update Balance with commission
			balanceDBManager.addBalance(new Balance(1, currComission, currAccount.getCustId()));
			flag = true;
		}

		return flag;
	}

	@Override
	public boolean depositWithoutComm(Account currAccount, double moneyAmount) throws DBManagerException {

		PreparedStatement pstmt = null;
		int rowsUpdated = 0; // number of updated rows
		boolean flag = false;
		Connection conn = getConnection(); // receive connection from Account

		// Manager

		try {
			pstmt = conn.prepareStatement("UPDATE accounts SET amount=(?) WHERE account_id=(?) AND cust_id =(?)");

			double newAmount = (currAccount.getAmount() + moneyAmount);

			pstmt.setString(1, doubleToString(newAmount));

			Long accountId = currAccount.getAccountId();
			pstmt.setString(2, longToString(accountId));

			Long custId = currAccount.getCustId();
			pstmt.setString(3, longToString(custId));

			rowsUpdated = pstmt.executeUpdate();

		} catch (SQLException e) {
			throw new DBManagerException("[AccountDBManager]: Data wasn't found, problem to add money");
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		if (rowsUpdated != 0) {
			System.out.println("[AccountDBManager]: Success, money was added to account");
			flag = true;
		}

		return flag;
	}

	@Override
	public boolean withdrawWithComm(Account currAccount, double moneyAmount) throws DBManagerException {

		BalanceDBManager balanceDBManager = getBalanceDBManager();
		boolean flag = false;

		// calculate commission for this type account and money amount
		double currComission = calculateComission(currAccount, moneyAmount);

		if (withdrawWithoutComm(currAccount, (moneyAmount + currComission))) {
			// Update Balance with commission
			balanceDBManager.addBalance(new Balance(1, currComission, currAccount.getCustId()));
			flag = true;
		}

		return flag;
	}

	@Override
	public boolean withdrawWithoutComm(Account currAccount, double moneyAmount) throws DBManagerException {

		PreparedStatement pstmt = null;
		int rowsUpdated = 0; // number of updated rows
		boolean flag = false;
		Connection conn = getConnection();

		if (currAccount.getAllowedWithdraw() < Math.abs(moneyAmount)) {
			// if customer try to take more money that his allowed credit
			throw new DBManagerException("[AccountDBManager]: Sorry, you can't get more money than your bank credit. You allowed credit is " + currAccount.getAllowedWithdraw());
		} else {

			// calculate new updated amount of money
			double newAmount = currAccount.getAmount() - moneyAmount;

			try {
				pstmt = conn.prepareStatement("UPDATE accounts SET amount=(?) WHERE account_id=(?) AND cust_id =(?)");

				pstmt.setString(1, doubleToString(newAmount));

				Long accountId = currAccount.getAccountId();
				pstmt.setString(2, longToString(accountId));

				Long custId = currAccount.getCustId();
				pstmt.setString(3, longToString(custId));

				rowsUpdated = pstmt.executeUpdate();

			} catch (SQLException e) {
				throw new DBManagerException("[AccountDBManager]: Data wasn't found, problem to add money");
			} finally {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}

			}

		}
		if (rowsUpdated != 0) {
			System.out.println("[AccountDBManager]: Money Withdraw done successfully. Account updated.");
			flag = true;
		}

		return flag;
	}

	@Override
	public Account getAccountById(long currAccountId) throws DBManagerException {
		// Receive Account object by account id number

		ResultSet rset = null;
		Statement statement = null;
		Account account = new Account(); // create new Account Object
		Connection conn = getConnection();// receive connection from Account
		// Manager

		try {

			statement = conn.createStatement();
			String query = ("SELECT * FROM accounts where account_id =" + currAccountId);
			rset = statement.executeQuery(query);

			if (rset.next()) {
				account.setAccountId(rset.getLong(1)); // set data fields for
				// account objects
				account.setCustId(rset.getLong(2));
				account.setAmount(rset.getDouble(3));
				account.setCredit(rset.getLong(4));
				account.setCommission(rset.getDouble(5));
				account.setComments(rset.getString(6));
			} else {

				throw new DBManagerException("[AccountDBManager]: Haven't object in DB with account_id =" + currAccountId);
			}
		} catch (SQLException e) {
			throw new DBManagerException("[AccountDBManager]: Cant't find Account by ID");

		} finally {
			try {
				rset.close();
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return account;
	}

}
